package core.util.database.index.helper;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;

import core.product.model.Product;
import core.product.model.ProductType;
import core.util.HibernateCoreUtil;

public class ProductTypeIndex 
{
	
	public static void runProductTypeIndex()
	{
		Session session = HibernateCoreUtil.getSessionFactory().openSession();
		Transaction transaction = null;
		List<ProductType> ptyplst = new ArrayList<ProductType>();
		String queryDelete = "drop table if exists product_type_index";
		String createTable = "create table product_type_index(productid bigint(20)" ;
		try 
		{
				transaction = session.beginTransaction();
				ptyplst = session.createQuery("from ProductType").list();
				Iterator<ProductType> ptItr = ptyplst.iterator();
				Query query = session.createSQLQuery(queryDelete);
				int a = query.executeUpdate();
				System.out.println(a + " rows effected");
				//creating query to create productType index....
				while(ptItr.hasNext())
				{
					String pt = "c" + ptItr.next().getUuid().toString();
					createTable = createTable + " ," + pt + " bit(1)" ;
				}
				createTable = createTable + ")";
				query = session.createSQLQuery(createTable);
				a = query.executeUpdate();
				System.out.println(a + " rows effected");
				Iterator <Product> prodItr = session.createQuery("from Product").list().iterator();
				while(prodItr.hasNext())
				{
					Product p = prodItr.next();
					Iterator<ProductType> prodTypeItr = p.getProductType().iterator();
					// check if product exists in product_type_index table
					SQLQuery prodCountquery = session.createSQLQuery("select count(*) count from  product_type_index where productid =" + p.getUuid()).addScalar("count", Hibernate.INTEGER);
					int cnt = (Integer) prodCountquery.list().get(0);
					if(cnt == 0)
					{
						//insert productId
						String insertQry ="insert into product_type_index(productid) values (" + p.getUuid() + ")" ;
						query = session.createSQLQuery(insertQry);
						query.executeUpdate();
					}
					while(prodTypeItr.hasNext())
					{
						ProductType pt = prodTypeItr.next();
						String cols = "c" + pt.getUuid();
						// query for update row
						String updateQry = "update product_type_index set " + cols + " = 1 where productid = " + p.getUuid();
						query = session.createSQLQuery(updateQry);
						query.executeUpdate();
					}
				}
				transaction.commit();
		} 
		catch (HibernateException e) 
		{
			// TODO: handle exception
			transaction.rollback();
			e.printStackTrace();
		}
		finally 
		{
			session.close();
		}
	}
	public static void main(String argv[])
	{
		System.out.println("Start running product type index");
		runProductTypeIndex();
	}
}
